<?php
namespace app\common\command;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\Db;

class DataReport extends Command
{
    protected function configure()
    {
        $this->setName('data_stats')
            ->addArgument('action', Argument::OPTIONAL, "方法名称")
            ->addOption('start_date', null, Option::VALUE_REQUIRED, '开始日期')
            ->addOption('end_date', null, Option::VALUE_REQUIRED, '结束日期')
            ->setDescription('订单收益数据汇总');
    }

    protected function execute(Input $input, Output $output)
    {

        $action = $input->getArgument('action');

        if (method_exists($this, $action)) {

            $start_date = $input->hasOption('start_date') ? $input->getOption('start_date') : date('Y-m-d', strtotime('-1days'));
            $end_date = $input->hasOption('end_date') ? $input->getOption('end_date') : date('Y-m-d', strtotime('-1days'));

            $this->$action($start_date, $end_date);
        }

    }

    /*订单每日汇总 - 投放号维度*/
    public function order_daily_channel($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        //充值金额汇总
        $sql = "SELECT 
                    channel_id,SUM(total_fee) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2
                GROUP by channel_id";

        //普通充值金额汇总
        $sql2 = "SELECT 
                    channel_id,SUM(total_fee) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND recharge_type = 1
                GROUP by channel_id";

        //活动充值金额汇总
        $sql3 = "SELECT 
                    channel_id,SUM(total_fee) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND recharge_type = 2
                GROUP by channel_id";

        //非VIP充值金额
        $sql4 = "SELECT 
                    channel_id,SUM(total_fee) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND type = 1
                GROUP by channel_id";

        //非VIP充值人数
        $sql5 = "SELECT
                    a.channel_id,COUNT( a.uid ) AS number 
                FROM
                    (
                SELECT
                    channel_id,uid
                FROM
                    `pay_order` 
                WHERE
                    create_date = ? AND `status` = 2 AND type = 1
                GROUP BY
                    channel_id,uid 
                    ) AS a 
                GROUP BY
                    a.channel_id";

        //非VIP已支付笔数
        $sql6 = "SELECT 
                    channel_id,COUNT(id) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND type = 1
                GROUP by channel_id";

        //非VIP未支付笔数
        $sql7 = "SELECT 
                    channel_id,COUNT(id) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` <> 2 AND type = 1
                GROUP by channel_id";

        //VIP充值金额
        $sql8 = "SELECT 
                    channel_id,SUM(total_fee) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND type = 2
                GROUP by channel_id";

        //VIP充值人数
        $sql9 = "SELECT
                    a.channel_id,COUNT( a.uid ) AS number 
                FROM
                    (
                SELECT
                    channel_id,uid
                FROM
                    `pay_order` 
                WHERE
                    create_date = ? AND `status` = 2 AND type = 2
                GROUP BY
                    channel_id,uid 
                    ) AS a 
                GROUP BY
                    a.channel_id";

        //VIP已支付笔数
        $sql10 = "SELECT 
                    channel_id,COUNT(id) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` = 2 AND type = 2
                GROUP by channel_id";

        //VIP未支付笔数
        $sql11 = "SELECT 
                    channel_id,COUNT(id) as number 
                FROM `pay_order` 
                WHERE create_date = ? AND `status` <> 2 AND type = 2
                GROUP by channel_id";

        foreach ($date_rang as $v) {

            $list   = Db::query($sql,[$v]);
            $list2  = Db::query($sql2,[$v]);
            $list2  = array_column($list2,null,'channel_id');
            $list3  = Db::query($sql3,[$v]);
            $list3  = array_column($list3,null,'channel_id');
            $list4  = Db::query($sql4,[$v]);
            $list4  = array_column($list4,null,'channel_id');
            $list5  = Db::query($sql5,[$v]);
            $list5  = array_column($list5,null,'channel_id');
            $list6  = Db::query($sql6,[$v]);
            $list6  = array_column($list6,null,'channel_id');
            $list7  = Db::query($sql7,[$v]);
            $list7  = array_column($list7,null,'channel_id');
            $list8  = Db::query($sql8,[$v]);
            $list8  = array_column($list8,null,'channel_id');
            $list9  = Db::query($sql9,[$v]);
            $list9  = array_column($list9,null,'channel_id');
            $list10  = Db::query($sql10,[$v]);
            $list10  = array_column($list10,null,'channel_id');
            $list11  = Db::query($sql11,[$v]);
            $list11  = array_column($list11,null,'channel_id');

            if($list){
            
                foreach ($list as $lv){

                    $record = Db::table('rp_order_daily_channel')
                        ->where(['channel_id'=>$lv['channel_id'],'create_date'=>$v])
                        ->field('id')->find();
                    $new_zc_money = Db::table('pay_order_success')->where("create_date", $v)->where("channel_id", $lv['channel_id'])->where("user_reg_date", '=', $v)->sum('total_fee') ?? 0;

                    if($record){

                        Db::table('rp_order_daily_channel')->where(['id'=>$record['id']])->update([
                            'money'                     => $lv['number'],
                            'ordinary_recharge_money'   => isset($list2[$lv['channel_id']]['number']) ? $list2[$lv['channel_id']]['number'] : 0,
                            'activity_recharge_money'   => isset($list3[$lv['channel_id']]['number']) ? $list3[$lv['channel_id']]['number'] : 0,
                            'non_vip_money'             => isset($list4[$lv['channel_id']]['number']) ? $list4[$lv['channel_id']]['number'] : 0,
                            'non_vip_user_num'          => isset($list5[$lv['channel_id']]['number']) ? $list5[$lv['channel_id']]['number'] : 0,
                            'non_vip_paid_num'          => isset($list6[$lv['channel_id']]['number']) ? $list6[$lv['channel_id']]['number'] : 0,
                            'non_vip_unpaid_num'        => isset($list7[$lv['channel_id']]['number']) ? $list7[$lv['channel_id']]['number'] : 0,
                            'vip_money'                 => isset($list8[$lv['channel_id']]['number']) ? $list8[$lv['channel_id']]['number'] : 0,
                            'vip_user_num'              => isset($list9[$lv['channel_id']]['number']) ? $list9[$lv['channel_id']]['number'] : 0,
                            'vip_paid_num'              => isset($list10[$lv['channel_id']]['number']) ? $list10[$lv['channel_id']]['number'] : 0,
                            'vip_unpaid_num'            => isset($list11[$lv['channel_id']]['number']) ? $list11[$lv['channel_id']]['number'] : 0,
                            'new_zc_money'              => $new_zc_money,
                        ]);

                    }else{

                        Db::table('rp_order_daily_channel')->insert([
                            'money'                     => $lv['number'],
                            'ordinary_recharge_money'   => isset($list2[$lv['channel_id']]['number']) ? $list2[$lv['channel_id']]['number'] : 0,
                            'activity_recharge_money'   => isset($list3[$lv['channel_id']]['number']) ? $list3[$lv['channel_id']]['number'] : 0,
                            'non_vip_money'             => isset($list4[$lv['channel_id']]['number']) ? $list4[$lv['channel_id']]['number'] : 0,
                            'non_vip_user_num'          => isset($list5[$lv['channel_id']]['number']) ? $list5[$lv['channel_id']]['number'] : 0,
                            'non_vip_paid_num'          => isset($list6[$lv['channel_id']]['number']) ? $list6[$lv['channel_id']]['number'] : 0,
                            'non_vip_unpaid_num'        => isset($list7[$lv['channel_id']]['number']) ? $list7[$lv['channel_id']]['number'] : 0,
                            'vip_money'                 => isset($list8[$lv['channel_id']]['number']) ? $list8[$lv['channel_id']]['number'] : 0,
                            'vip_user_num'              => isset($list9[$lv['channel_id']]['number']) ? $list9[$lv['channel_id']]['number'] : 0,
                            'vip_paid_num'              => isset($list10[$lv['channel_id']]['number']) ? $list10[$lv['channel_id']]['number'] : 0,
                            'vip_unpaid_num'            => isset($list11[$lv['channel_id']]['number']) ? $list11[$lv['channel_id']]['number'] : 0,
                            'new_zc_money'              => $new_zc_money,
                            'channel_id'                => $lv['channel_id'],
                            'create_date'               => $v
                        ]);
                    }

                }

            }
        }

    }

    /*订单每日汇总 - 代理维度*/
    public function order_daily_agent($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        $roleId = Db::table('cms_manager_role')->where('alias','agent')->value('id');
        $agents = Db::table('cms_manager_user')->where('role_id',$roleId)->column('id');
        $agents[] = 0;
        foreach ($agents as $agent_id)
        {
            $channelIds = Db::table('cms_manager_user')->where('parent_id',$agent_id)->column('id');
            if ($agent_id == 0){
                $channelIds = [0];
            }
            if(empty($channelIds))
            {
                continue;
            }

            $channelIdStr = implode(',',$channelIds);

            foreach ($date_rang as $v) {

                $sql = "SELECT 
                    create_date,SUM(money) as money,SUM(ordinary_recharge_money) as ordinary_recharge_money,
                    SUM(activity_recharge_money) as activity_recharge_money,SUM(non_vip_money) as non_vip_money,
                    SUM(non_vip_user_num) as non_vip_user_num,SUM(non_vip_paid_num) as non_vip_paid_num,
                    SUM(non_vip_unpaid_num) as non_vip_unpaid_num,SUM(vip_money) as vip_money,
                    SUM(vip_user_num) as vip_user_num,SUM(vip_paid_num) as vip_paid_num,
                    SUM(vip_unpaid_num) as vip_unpaid_num
                FROM `rp_order_daily_channel` 
                WHERE create_date = '{$v}' AND channel_id in ({$channelIdStr})
                GROUP by create_date";

                $result = Db::query($sql);

                if($result){

                    $record = Db::table('rp_order_daily_agent')
                        ->where(['create_date'=>$v,'agent_id'=>$agent_id])
                        ->field('id')->find();
                    $new_zc_money = Db::table('pay_order_success')->where("create_date", $v)->where("agent_id", '=', $agent_id)->where("user_reg_date", '=', $v)->sum('total_fee') ?? 0;

                    if($record){

                        Db::table('rp_order_daily_agent')->where(['id'=>$record['id']])->update([
                            'money'                     => $result[0]['money'],
                            'ordinary_recharge_money'   => $result[0]['ordinary_recharge_money'],
                            'activity_recharge_money'   => $result[0]['activity_recharge_money'],
                            'non_vip_money'             => $result[0]['non_vip_money'],
                            'non_vip_user_num'          => $result[0]['non_vip_user_num'],
                            'non_vip_paid_num'          => $result[0]['non_vip_paid_num'],
                            'non_vip_unpaid_num'        => $result[0]['non_vip_unpaid_num'],
                            'vip_money'                 => $result[0]['vip_money'],
                            'vip_user_num'              => $result[0]['vip_user_num'],
                            'vip_paid_num'              => $result[0]['vip_paid_num'],
                            'vip_unpaid_num'            => $result[0]['vip_unpaid_num'],
                            'new_zc_money'              => $new_zc_money,
                        ]);

                    }else{

                        Db::table('rp_order_daily_agent')->insert([
                            'money'                     => $result[0]['money'],
                            'ordinary_recharge_money'   => $result[0]['ordinary_recharge_money'],
                            'activity_recharge_money'   => $result[0]['activity_recharge_money'],
                            'non_vip_money'             => $result[0]['non_vip_money'],
                            'non_vip_user_num'          => $result[0]['non_vip_user_num'],
                            'non_vip_paid_num'          => $result[0]['non_vip_paid_num'],
                            'non_vip_unpaid_num'        => $result[0]['non_vip_unpaid_num'],
                            'vip_money'                 => $result[0]['vip_money'],
                            'vip_user_num'              => $result[0]['vip_user_num'],
                            'vip_paid_num'              => $result[0]['vip_paid_num'],
                            'vip_unpaid_num'            => $result[0]['vip_unpaid_num'],
                            'new_zc_money'              => $new_zc_money,
                            'create_date'               => $v,
                            'agent_id'                  => $agent_id
                        ]);
                    }

                }
            }
        }

    }

    /*订单每日汇总 - 运营或超管维度*/
    public function order_daily($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        //每日数据汇总
        $sql = "SELECT 
                    create_date,SUM(money) as money,SUM(ordinary_recharge_money) as ordinary_recharge_money,
                    SUM(activity_recharge_money) as activity_recharge_money,SUM(non_vip_money) as non_vip_money,
                    SUM(non_vip_user_num) as non_vip_user_num,SUM(non_vip_paid_num) as non_vip_paid_num,
                    SUM(non_vip_unpaid_num) as non_vip_unpaid_num,SUM(vip_money) as vip_money,
                    SUM(vip_user_num) as vip_user_num,SUM(vip_paid_num) as vip_paid_num,
                    SUM(vip_unpaid_num) as vip_unpaid_num
                FROM `rp_order_daily_channel` 
                WHERE create_date = ?
                GROUP by create_date";

        foreach ($date_rang as $v) {

            $list   = Db::query($sql,[$v]);

            if($list){

                foreach ($list as $lv){

                    $record = Db::table('rp_order_daily')->where(['create_date'=>$v])->field('id')->find();
                    $new_zc_money = Db::table('pay_order_success')->where("create_date", $v)->where("user_reg_date", '=', $v)->sum('total_fee') ?? 0;

                    if($record){

                        Db::table('rp_order_daily')->where(['id'=>$record['id']])->update([
                            'money'                     => $lv['money'],
                            'ordinary_recharge_money'   => $lv['ordinary_recharge_money'],
                            'activity_recharge_money'   => $lv['activity_recharge_money'],
                            'non_vip_money'             => $lv['non_vip_money'],
                            'non_vip_user_num'          => $lv['non_vip_user_num'],
                            'non_vip_paid_num'          => $lv['non_vip_paid_num'],
                            'non_vip_unpaid_num'        => $lv['non_vip_unpaid_num'],
                            'vip_money'                 => $lv['vip_money'],
                            'vip_user_num'              => $lv['vip_user_num'],
                            'vip_paid_num'              => $lv['vip_paid_num'],
                            'vip_unpaid_num'            => $lv['vip_unpaid_num'],
                            'new_zc_money'              => $new_zc_money,
                        ]);

                    }else{

                        Db::table('rp_order_daily')->insert([
                            'money'                     => $lv['money'],
                            'ordinary_recharge_money'   => $lv['ordinary_recharge_money'],
                            'activity_recharge_money'   => $lv['activity_recharge_money'],
                            'non_vip_money'             => $lv['non_vip_money'],
                            'non_vip_user_num'          => $lv['non_vip_user_num'],
                            'non_vip_paid_num'          => $lv['non_vip_paid_num'],
                            'non_vip_unpaid_num'        => $lv['non_vip_unpaid_num'],
                            'vip_money'                 => $lv['vip_money'],
                            'vip_user_num'              => $lv['vip_user_num'],
                            'vip_paid_num'              => $lv['vip_paid_num'],
                            'vip_unpaid_num'            => $lv['vip_unpaid_num'],
                            'new_zc_money'              => $new_zc_money,
                            'create_date'               => $v
                        ]);
                    }

                }

            }
        }

    }

}